<?php
/**
 * 快递模板
 * AUTHOR  : zlf
 * DateTime: 2022/07/06 09:00
 */
namespace App\V3\V3_2_7\Controllers;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use PhpOffice\PhpSpreadsheet\IOFactory;
use App\V3\V3_2_7\Models\SystemConfigModel;
use App\Helper\DBRSAUtil;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use App\V3\V3_2_7\Models\ExpressTempleteModel;
use App\V3\V3_2_7\Models\ExpressTempleteRuleModel;
use App\V3\V3_2_7\Models\ExpressTempleteIntervalModel;
use App\V3\V3_2_7\Models\KuaidiModel;
use App\V3\V3_2_7\Models\ExpressTempleteClassModel;
use App\V3\V3_2_7\Services\ExpressTempleteService;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use App\Services\CommonService;

class CreateExpressTemplatesController extends Controller
{
    /**
     * 快递模板导入
     * @param express_templete  快递模板文件
     *
     */
    public function importExpressTemplates(Request $request){

        $token = $request->header('token');
        //$token='4637 uCKUl6GTFj/WcrYI0IR2n5/jOy7n2f+7y8QHHXB+5M4RlisgShrokhxYj7ftZU3K3C4GAWa/Y8RpdKE0nG55pl2AuGxupw1asd8UMkblSg16Xudo5x3ScWOBqX2zdjvAzSZ4CJjske+HmxEtZ4I9nD9Fy7jgV5M07DFe/jJEAvU=';
        if(empty($token)){
             return response()->json(['code' => 2,'msg' => 'token不能为空','data' => '']);
        }
        $array = explode(' ',$token);
        //私钥获取
        $private = SystemConfigModel::getConfigValue('private');
        $rsadata = DBRSAUtil::rsaDecrypt($array[1], $private);
        $rsadata = json_decode($rsadata,true);
        $files = $request->file('express_templete');

        $ext = $files->extension();
       /*  $ext =$files->getClientOriginalExtension(); */

        if($files->getSize() > 10 * 1024 * 1024)
        {
            return response()->json(['code' => 1,'msg'  => '文件大小不能超出10M','data' => '']);
        }

        //application/vnd.ms-excel  为xls或xlsx文件类型
        if (!in_array($ext, ['xls', 'xlsx'])) {
            return response()->json(['code' => 1,'msg' => '必须为excel表格，且必须为xlsx或xls格式！','data' => '']);
        }

        if($ext == 'xls') {
            $reader = IOFactory::createReader('Xls');
        }
        else if($ext == 'xlsx') {
            $reader = IOFactory::createReader('Xlsx');
        }

        $path = $files->store(date('Y').'/'.date('m').'/'.date('d'));
        $filePath     = public_path().'/uploads/'.$path;

        $objPHPExcel  = $reader->load($filePath);
        $excelSheet   = $objPHPExcel->getSheet(0);//读取第一个工作表
        $highestColumn = $excelSheet->getHighestColumn();   // 取得总列数
        $highestRow = $excelSheet->getHighestRow();// 取得总行数
        $userData = [];
        $eData = [];
        $i=0;
        $e=0;
        $n = $objPHPExcel->getActiveSheet()->getCell('A1')->getValue();

        if(strpos($n,'编号') !== false){

            for ($j = 2; $j <= $highestRow; $j++) {

                $n = $j-2;

                $eData[$n] = [
                    'express_number'    => $objPHPExcel->getActiveSheet()->getCell('A' . $j)->getValue(),
                    'express_name'      => $objPHPExcel->getActiveSheet()->getCell('B' . $j)->getValue(),
                    'throw_weight_than' => $objPHPExcel->getActiveSheet()->getCell('C'. $j)->getValue(),
                    'from_province'     => $objPHPExcel->getActiveSheet()->getCell('D'. $j)->getValue(),
                    'from_city'         => $objPHPExcel->getActiveSheet()->getCell('E'. $j)->getValue(),
                    'to_province'       => $objPHPExcel->getActiveSheet()->getCell('F'. $j)->getValue(),
                    'to_city'           => $objPHPExcel->getActiveSheet()->getCell('G'. $j)->getValue(),
                    'first_amount'      => $objPHPExcel->getActiveSheet()->getCell('H'. $j)->getValue(),
                    'total_weight'      => $objPHPExcel->getActiveSheet()->getCell('I'. $j)->getValue(),
                    'first_heavy'       => $objPHPExcel->getActiveSheet()->getCell('J'. $j)->getValue(),
                ];

                for($t='K'; $t<=$highestColumn; $t++){

                    $qj =$objPHPExcel->getActiveSheet()->getCell($t . $j)->getValue();
                    if(!empty($qj)){

                        if(stripos($qj,'-')!==false && stripos($qj,'=') === false){
                            $i++;
                        }
                        if(stripos($qj,'+')!==false && stripos($qj,'=') === false){
                            $i++;
                            $eData[$n]['qj_list'][$i][$e] =$qj;
                            $eData[$n]['qj_list'][$i][++$e] =$objPHPExcel->getActiveSheet()->getCell(++$t . $j)->getValue();
                            $e=0;
                            $i=0;
                            break;
                        }
                        if(stripos($qj,'=') ===false){

                            $eData[$n]['qj_list'][$i][] =$qj;

                        }

                    }else{
                        $eData[$n]['qj_list'][$i][] ="";
                        break;
                    }
                }
            }
        }else{
            for ($j = 2; $j <= $highestRow; $j++) {

            $n = $j-2;

             $eData[$n] = [
                'express_name'      => $objPHPExcel->getActiveSheet()->getCell('A' . $j)->getValue(),
                'throw_weight_than' => $objPHPExcel->getActiveSheet()->getCell('B'. $j)->getValue(),
                'from_province'     => $objPHPExcel->getActiveSheet()->getCell('C'. $j)->getValue(),
                'from_city'         => $objPHPExcel->getActiveSheet()->getCell('D'. $j)->getValue(),
                'to_province'       => $objPHPExcel->getActiveSheet()->getCell('E'. $j)->getValue(),
                'to_city'           => $objPHPExcel->getActiveSheet()->getCell('F'. $j)->getValue(),
                'first_amount'      => $objPHPExcel->getActiveSheet()->getCell('G'. $j)->getValue(),
                'total_weight'      => $objPHPExcel->getActiveSheet()->getCell('H'. $j)->getValue(),
                'first_heavy'       => $objPHPExcel->getActiveSheet()->getCell('I'. $j)->getValue(),
            ];

             for($t='J'; $t<=$highestColumn; $t++){

                 $qj =$objPHPExcel->getActiveSheet()->getCell($t . $j)->getValue();
                 if(!empty($qj)){

                     if(stripos($qj,'-')!==false && stripos($qj,'=') === false){
                         $i++;
                     }
                     if(stripos($qj,'+')!==false && stripos($qj,'=') === false){
                         $i++;
                         $eData[$n]['qj_list'][$i][$e] =$qj;
                         $eData[$n]['qj_list'][$i][++$e] =$objPHPExcel->getActiveSheet()->getCell(++$t . $j)->getValue();
                         $e=0;
                         $i=0;
                        break;
                     }
                     if(stripos($qj,'=') ===false){

                     $eData[$n]['qj_list'][$i][] =$qj;

                     }

                 }else{
                     break;
                 }
             }
            }
        }

        if(empty($eData)) {
           return response()->json(['code' => 0,'msg' => 'success','data' => '']);
        }

         DB::beginTransaction();

         try {
            $arr = [];
            $count = count($eData);
            for($p = 0;$p < $count; $p++) {
                $a = $eData[$p];
                unset($eData[$p]);
                if(!in_array($a, $eData)) {
                    $arr[] = $a;
                }
            };

            foreach($arr as $key => $value) {

                if(!is_numeric($value['first_amount'])) {
                    return response()->json(['code' => 1,'msg' => '导入失败。原因：首重金额只能为数字','data' => '']);
                }

                if($value['first_amount'] < 0) {
                    return response()->json(['code' => 1,'msg' => '导入失败。原因：首重金额不能为负数','data' => '']);
                }

                if(!is_numeric($value['total_weight'])) {
                    return response()->json(['code' => 1,'msg' => '导入失败。原因：总重量只能为数字','data' => '']);
                }

                if($value['total_weight'] < 0) {
                    return response()->json(['code' => 1,'msg' => '导入失败。原因：总重量必须大于0','data' => '']);
                }

                $tData = [];

                $kInfo = KuaidiModel::select('id','company_name')->where(['company_name'=>$value['express_name']])->first();

                if(isset($value['express_number'])){

                    $rInfo = ExpressTempleteRuleModel::where(['id'=>$value['express_number'],'main_id'=>$rsadata['mainUserId']])->first();

                    if(!empty($rInfo)){

                        $rId =$rInfo['id'];
                        $cId =$rInfo['templete_class_id'];
                        $tId =$rInfo['express_templete_id'];
                    }
                }
                $eCondition = [
                    'et.from_province' => $value['from_province'],
                    'et.from_city'     => $value['from_city'],
                    'et.kuaidi_name' => $value['express_name'],
                    'et.main_id'     => $rsadata['mainUserId']
                ];

                $eInfo = ExpressTempleteModel::select('et.id as tId','etc.id as cId')
                ->from('express_templete as et')
                ->leftJoin('express_templete_class as etc','etc.express_templete_id','=','et.id')
                ->where($eCondition)
                ->where('et.is_del','0')
                ->first();

                if(!empty($eInfo)){

                    $tId = $eInfo['tId'];
                    $cId = $eInfo['cId'];

                }else{

                    $tData['kuaidi_id']     = $kInfo['id'];
                    $tData['kuaidi_name']   = $value['express_name'];
                    $tData['user_id']       = $rsadata['userId'];
                    $tData['main_id']       = $rsadata['mainUserId'];
                    $tData['from_province'] = !empty($value['from_province'])?$value['from_province']:'安徽';
                    $tData['from_city']     = !empty($value['from_city'])?$value['from_city']:'蚌埠';

                    $tId = ExpressTempleteModel::insertGetId($tData);
                    $cId = ExpressTempleteClassModel::insertGetId([
                        'templete_class_name' => $kInfo['company_name'],
                        'express_templete_id' => $tId,
                        'created_at'          => time()
                    ]);
                }

                $rData = [];
                $rData['templete_class_id']  = $cId;
                $rData['express_templete_id']= $tId;
                $rData['to_province']        = $value['to_province'];
                $rData['to_city']            = $value['to_city'];
                $rData['basic_weight']       = $value['first_heavy'];
                $rData['basic_weight_price'] = $value['first_amount'];
                $rData['user_id']            = $rsadata['userId'];
                $rData['main_id']            = $rsadata['mainUserId'];
                $rData['total_weight']       = $value['total_weight'];
                $rData['rule_title']         = $value['from_city'].'-'.$value['to_city'];
                $rData['from_city']          = !empty($value['from_city'])?$value['from_city']:'蚌埠';
                $rData['from_province']      = !empty($value['from_province'])?$value['from_province']:'安徽';
                $rData['weight_throw']       = !empty($value['throw_weight_than'])?$value['throw_weight_than']:"0.00";

              if(!empty($rInfo)){

                  ExpressTempleteRuleModel::where(['id'=>$rId,'main_id'=>$rsadata['mainUserId']])->update($rData);

              }else{

                $rId = ExpressTempleteRuleModel::insertGetId($rData);
              }

              if(!empty($rInfo)){

                  $iCondition['templete_rule_id']    = $rId;
                  $iCondition['express_templete_id'] = $tId;
                  $iInfo = ExpressTempleteIntervalModel::select('id')->where($iCondition)->orderBy('id','asc')->get()->toArray();

              }

                if(!empty($value['qj_list'])){

                    foreach ($value['qj_list'] as $qk=>$qv){

                        $iData = [];
                        if(strpos($qv[0],'-')!==false){

                            $qj_arr=explode('-',$qv[0]);
                            $iData['max_weight'] = $qj_arr[1];
                        }
                        if(strpos($qv[0],'+')!==false){
                            $qj_arr=explode('+',$qv[0]);
                            $iData['max_weight'] ='99999999.99';
                        }
                        $iData['min_weight'] = !empty($qj_arr[0])?$qj_arr[0]:"";
                        $iData['price']      = $qv[1];
                        $iData['templete_rule_id']    = $rId;
                        $iData['express_templete_id'] = $tId;
                        if(!empty($rInfo)){
                            $icondition=[];
                            if(isset($iInfo[$qk-1]['id'])){

                            $icondition['id'] = $iInfo[$qk-1]['id'];
                            $icondition['templete_rule_id']    = $rId;
                            $icondition['express_templete_id'] = $tId;

                            ExpressTempleteIntervalModel::where($icondition)->update($iData);
                            }

                        }else{

                            ExpressTempleteIntervalModel::insertGetId($iData);
                        }


                    }

                }
                unset($tData);unset($rData);unset($iData);unset($kInfo);

            };
            $eData['main_id'] = $rsadata['mainUserId'];
            $eData['user_id'] = $rsadata['userId'];
            $logData = '共导入'.($highestRow-1).'条';
            CommonService::log('导入快递模板', $eData, $logData);
            DB::commit();
            unlink($filePath);
            return response()->json(['code' => 0,'msg' => 'success','data' => '']);
        }catch (\Exception $e) {
            DB::rollBack();
            Log::debug('导入总快递模板信息excel异常:'.var_export($e->getMessage(), true));
            return response()->json(['code' => 1,'msg' => '导入总快递模板信息excel时发生异常，请稍后再试','data' => '']);
        }
    }

    /**
     * 快递模板导出
     * @param express_name
     */
    public function exportExpressTemplates(Request $request){

         $data  = $request->all();
         $token = $data['token'];
         //$token='4637 uCKUl6GTFj/WcrYI0IR2n5/jOy7n2f+7y8QHHXB+5M4RlisgShrokhxYj7ftZU3K3C4GAWa/Y8RpdKE0nG55pl2AuGxupw1asd8UMkblSg16Xudo5x3ScWOBqX2zdjvAzSZ4CJjske+HmxEtZ4I9nD9Fy7jgV5M07DFe/jJEAvU=';
        if(empty($token)){
             return response()->json(['code' => 2,'msg' => 'token不能为空','data' => '']);
        }
        $eData=[];$kd_name ="";
        $array = explode(' ',$token);
        //私钥获取
        $private = SystemConfigModel::getConfigValue('private');
        $rsadata = DBRSAUtil::rsaDecrypt($array[1], $private);
        $rsadata = json_decode($rsadata,true);
        $condition['etr.main_id']=  $rsadata['mainUserId'];

        if(!empty($kd_name = $request->input('express_name'))){
        $condition['etr.kuaidi_name']=$kd_name;
        }
       
        $exportData = ExpressTempleteService::kdList($condition);

        if(!empty($exportData)){

            foreach ($exportData as $ke=>$ve){

                    $eData[$ke]['from_city']          = $ve['from_city'];
                    $eData[$ke]['kuaidi_name']        = $ve['kuaidi_name'];
                    $eData[$ke]['from_province']      = $ve['from_province'];
                    $eData[$ke]['kd_id']              = $ve['kd_id'];
                    $eData[$ke]['weight_throw']       = $ve['weight_throw'];
                    $eData[$ke]['to_city']            = $ve['to_city'];
                    $eData[$ke]['to_province']        = $ve['to_province'];
                    $eData[$ke]['total_weight']       = $ve['total_weight'];
                    $eData[$ke]['basic_weight']       = $ve['basic_weight'];
                    $eData[$ke]['basic_weight_price'] = $ve['basic_weight_price'];

                    $iInfo= ExpressTempleteIntervalModel::select('min_weight','max_weight','price')
                    ->where(
                        ['templete_rule_id'   => $ve['kd_id'],
                         'express_templete_id'=> $ve['et_id']]
                        )->get()->toArray();

                   foreach ($iInfo as $ki=>$vi){
                       $nn = $ki+1;
                       if($vi['max_weight']=='99999999.99'){

                       $qj = intval($vi['min_weight']).'+';
                       }else{
                       $qj = intval($vi['min_weight']).'-'.intval($vi['max_weight']);
                       }
                       $eData[$ke]['qj_'.$nn]       = $qj;
                       $eData[$ke]['qj_price_'.$nn] = $vi['price'];
                   }
            }
        }

        $exportData = $eData;
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();

        //设置表头
        $head = [
            '快递编号(不可修改)',
            '快递名称',
            '抛重比',
            '起始省',
            '起始市',
            '到达省',
            '到达市',
            '首重金额(元)',
            '总重量(kg)',
            '首重(kg)'
        ];

        //数据中对应的字段，用于读取相应数据
        $keys = [
            'kd_id',
            'kuaidi_name',
            'weight_throw',
            'from_province',
            'from_city',
            'to_province',
            'to_city',
            'basic_weight_price',
            'total_weight',
            'basic_weight'
        ];

        $interval_count = 1;

        if(!empty($exportData)){

            foreach($exportData as $key => $value) {

                $num[]=(count($value)-10)/2;

            }
                $interval_count = !empty(max($num))?max($num):1;
        }

        for($i=1;$i <= $interval_count; $i++){
            if($i<10){
                $n='0'.$i;
            }else{
                $n=$i;
            }

            $head = array_merge($head,['区间-'.$n.'（kg）']);
            $head = array_merge($head,['续重金额-'.$n.'(元)']);
            $keys=array_merge($keys,['qj_'.$i]);
            $keys=array_merge($keys,['qj_price_'.$i]);
        }

        $count = count($head);//计算表头数量
        for($i = 65; $i< $count + 65; $i++) {//数字转字母从65开始，循环设置表头

            $sheet->setCellValue(strtoupper(chr($i)).'1', $head[$i - 65]);
        }
        if(empty($exportData)){
             //默认数据
            $exportData[0] = [];
           }
        /*-------------------开始从数据库提取信息插入Excel表中-------------------------*/
        foreach($exportData as $key => $value) {
            if($key/300 == 0) {
                ob_flush();
                flush();
            }

            for($i = 65; $i < $count + 65; $i++) {

                $spreadsheet->getActiveSheet()->setTitle($kd_name.'快递模板_'.date('Y-m-d'));//设置标题
                //设置标题背景颜色
                $spreadsheet->getActiveSheet()->getStyle(strtoupper(chr($i)).'1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB(Color::COLOR_GREEN);
                $spreadsheet->getActiveSheet()->setCellValueExplicit(strtoupper(chr($i)).($key + 2), empty($value[$keys[$i - 65]])?'暂无':$value[$keys[$i - 65]], DataType::TYPE_STRING);
                $spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setWidth(20);//固定列宽
            }
        }
        $data['main_id'] = $rsadata['mainUserId'];
        $data['user_id'] = $rsadata['userId'];
        CommonService::log($kd_name.'快递模板', $data,'导出共'.count($exportData).'条');
        $fileName = '睡猫云仓_'.$kd_name.'快递模板_'.date('Ymd').'.xlsx';      //文件名

        header('Content-Type: application/vnd.ms-excel; charset=utf-8');    //输出文件类型
        header('Content-Disposition: attachment;filename="'.$fileName.'"');    //输出文件名
        header('Cache-Control: max-age=0');    //强制每次请求直接发送给源服务器，而不经过本地缓存版本的校验
        header('Access-Control-Allow-Origin:*');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');

        //清空
        $spreadsheet->disconnectWorksheets();

        unset($spreadsheet);
        exit;
    }

    /**
     * 下载快递模板
     */
    public function download()
    {
        ob_end_clean();
        $headers = [
            'Content-Type: application/xlsx',
        ];
        return response()->download(public_path().'/kd_temp.xlsx','kd_temp.xlsx',$headers);
    }
}
